CREATE OR REPLACE PACKAGE BODY pkg_pond_info
IS
    PROCEDURE new_pond (
       p_flag          OUT   PLS_INTEGER
      ,p_ret_msg       OUT   VARCHAR2 
      ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER
      ,p_pond_name           VARCHAR2 
      ,p_area                NUMBER 
      ,p_height              NUMBER 
      ,p_prod                VARCHAR2 
      ,p_density             NUMBER 
      ,p_source              VARCHAR2  
      ,p_earth_thickness     NUMBER 
        )
    IS
        v_flag         PLS_INTEGER := 0 ;
        v_msg          VARCHAR2(4000);
        v_date         VARCHAR2(4000);
        v_ct           PLS_INTEGER := 0 ;
    BEGIN
        -- 同一用户名下塘口名称不能重复 
        SELECT COUNT(1) 
            INTO     v_ct
        FROM pond_info 
        WHERE user_id  = p_user_id 
        AND   upper(pond_name) = upper(p_pond_name) 
        ;
        
        IF v_ct > 0 THEN 
            v_flag := -1;
            v_msg  := '同一用户名下塘口名称不能重复';
        ELSE 
            INSERT INTO pond_info (
                             pond_id         --  NUMBER(9,0)          NOT NULL 
                            ,pond_name       --   VARCHAR2(30 CHAR)    NOT NULL 
                            ,user_id         --  NUMBER (9,0)  
                            ,prod            --  VARCHAR2(12 CHAR)
                            ,source          --  VARCHAR2(12 CHAR )
                            ,area            --  NUMBER(7,1)  -- 单位：亩
                            ,height          --  NUMBER(5,1)  -- 单位： 米
                            ,density         --  NUMBER(7,2) 
    --                        ,built_dt        --  DATE    --- 计算池塘年龄用  
    --                        ,oxy_method      --  VARCHAR2(12 CHAR )
    --                        ,floor_inf       --  VARCHAR2(12 CHAR )
    --                        ,ceil_info       --  VARCHAR2(12 CHAR )
                            ,earth_thickness --  NUMBER ( 5,2)  -- 单位： 米
    --                        ,discharge_mod   --  VARCHAR2(12 CHAR )
    --                        ,oxy_power       --  NUMBER ( 7,2) 
    --                        ,alarm_call1     --  VARCHAR2(11 BYTE )
    --                        ,alarm_call2     --  VARCHAR2(11 BYTE )
                        )
                    VALUES ( pond_id_seq.NEXTVAL 
                            ,p_pond_name          -- VARCHAR2 
                            ,p_user_id            -- PLS_INTEGER
                            ,p_prod               -- VARCHAR2 
                            ,p_source             -- VARCHAR2  
                            ,decode(p_area  ,         0, null, p_area           )   -- NUMBER 
                            ,decode(p_height,         0, null, p_height         )   -- NUMBER 
                            ,decode(p_density,        0, null, p_density        )   -- NUMBER 
                            ,decode(p_earth_thickness,0, null, p_earth_thickness)   -- NUMBER 
                        );
            COMMIT;
            
            v_flag := 0;
            v_msg  := '已新增塘口信息';
        END IF ;
        p_flag      := v_flag;
        p_ret_msg   := v_msg ;
        p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }' ;
        
        RETURN;
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN 
            p_flag := -1;
            p_ret_msg  := '添加失败，相同塘口已经存在';
            p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }' ;
        WHEN OTHERS  THEN 
            p_flag := -2 ;
            p_ret_msg := '添加塘口失败，未知错误：'|| replace( sqlerrm(), '"', '');
            p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }' ;
            
    END new_pond  ;
    
    
    

   PROCEDURE pond_list (
       p_flag    OUT   PLS_INTEGER
      ,p_ret_msg       OUT   VARCHAR2 
      ,p_pond_list     OUT   SYS_REFCURSOR 
      ,p_user_id       PLS_INTEGER 
      )
    IS
        v_flag         PLS_INTEGER := 0 ;
        v_msg          VARCHAR2(4000 CHAR );    
        v_data         VARCHAR2(4000 CHAR);    

    BEGIN
	 
	 OPEN p_pond_list 
	 FOR 
        WITH d AS (
            SELECT pond_id,
                   '[ '||
                   listagg( '{"device_id":"'   || device_id  ||'"'||
                             ',"device_type":"'|| device_type||'"'||
                             ',"sim":"'        || sim        ||'"'||
                             '}', ',')
                       WITHIN GROUP ( ORDER BY device_id )             
                   ||']'  AS dev_list  
            FROM pond_info
			INNER JOIN 	device_list d
                  USING(pond_id)
            WHERE user_id = p_user_id 
            GROUP BY pond_id       
        )
        SELECT 
             '{"pond_id":"'        || pond_id        ||'"'||
                          ',"pond_name":"'      || pond_name      ||'"'||
                          ',"area":"'           || area           ||'"'||
                          ',"height":"'         || height         ||'"'||
                          ',"prod":"'           || prod           ||'"'||
                          ',"density":"'        || density        ||'"'||
                          ',"source":"'         || source         ||'"'||
                          ',"earth_thickness":"'|| earth_thickness||'"'||
                          ',"device_list":'     || NVL(dev_list,'[]') ||
                          '}' 
        FROM pond_info p
        LEFT JOIN  d USING(pond_id)
        WHERE user_id = p_user_id 
		ORDER BY pond_id DESC;
                        
        p_flag := 0;
        
        p_ret_msg := '查询塘口清单成功';

        
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND  THEN 
            p_flag :=  1;
            p_ret_msg  := '查询失败，该用户名下没有任何塘口';
			OPEN p_pond_list 
			FOR SELECT ' ' 
			FROM DUAL 
			WHERE NULL IS NOT NULL ;
			            
        WHEN OTHERS  THEN 
            p_flag := -2 ;
            p_ret_msg := '查询塘口失败，未知错误：'|| replace( sqlerrm(), '"', '');
			OPEN p_pond_list
			FOR SELECT ' ' 
			FROM DUAL 
			WHERE NULL IS NOT NULL ;
			            
    END pond_list ;    

/*
--------------由于太多塘口会导致返回的字串超长被截断，成为无郊和JSON串，故限制只返回最新三个塘口	
-----------    该问题已解决，请参见同名过程（ 用游标返回，代替JSON串返回）	
   PROCEDURE pond_list (
       p_flag    OUT   PLS_INTEGER
      ,p_ret_msg       OUT   VARCHAR2 
      ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id       PLS_INTEGER 
      )
    IS
        v_flag         PLS_INTEGER := 0 ;
        v_msg          VARCHAR2(4000 CHAR );    
        v_data         VARCHAR2(4000 CHAR);    

    BEGIN

        WITH d AS (
            SELECT pond_id,
                   '[ '||
                   listagg( '{"device_id":"'   || device_id  ||'"'||
                             ',"device_type":"'|| device_type||'"'||
                             ',"sim":"'        || sim        ||'"'||
                             '}', ',')
                       WITHIN GROUP ( ORDER BY device_id )             
                   ||']'  AS dev_list  
            FROM pond_info
			INNER JOIN 	device_list d
                  USING(pond_id)
            WHERE user_id = p_user_id 
            GROUP BY pond_id       
        )
        ,P AS ( 
            SELECT t.*, rownum rt 
            FROM pond_info t
            WHERE user_id = p_user_id 
            ORDER BY pond_id DESC
        )		
        SELECT 
              '['||
                listagg(  '{"pond_id":"'        || pond_id        ||'"'||
                          ',"pond_name":"'      || pond_name      ||'"'||
                          ',"area":"'           || area           ||'"'||
                          ',"height":"'         || height         ||'"'||
                          ',"prod":"'           || prod           ||'"'||
                          ',"density":"'        || density        ||'"'||
                          ',"source":"'         || source         ||'"'||
                          ',"earth_thickness":"'|| earth_thickness||'"'||
                          ',"device_list":'     || NVL(dev_list,'[]') ||
                          '}' , ',' ) 
                      WITHIN GROUP ( ORDER BY rt ) -- pond_name )
                  ||']'
            INTO     v_data          
        FROM p -- pond_info p
        LEFT JOIN  d USING(pond_id)
        WHERE user_id = p_user_id 
		AND  rt < 4                 ---------------------------------------由于太多塘口会导致返回的字串超长被截断，成为无郊和JSON串，故限制只返回最新三个塘口
		                             -----------------------------------    该问题已解决，请参见同名过程（ 用游标返回，代替JSON串返回）
		ORDER BY rt DESC;
                        
        p_flag := 0;
        
        p_ret_msg := '查询塘口清单成功';

        p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'","data": '||v_data ||'}' ;
        
        
        
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND  THEN 
            p_flag :=  1;
            p_ret_msg  := '查询失败，该用户名下没有任何塘口';
            p_data_msg := NULL;
        WHEN OTHERS  THEN 
            p_flag := -2 ;
            p_ret_msg := '查询塘口失败，未知错误：'|| replace( sqlerrm(), '"', '');
            p_data_msg := NULL;
            
    END pond_list ;    
*/


	PROCEDURE modify_pond (
       p_flag          OUT   PLS_INTEGER
      ,p_ret_msg       OUT   VARCHAR2 
      ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER 
      ,p_pond_id             PLS_INTEGER 
      ,p_pond_name             VARCHAR2
      ,p_prod                  VARCHAR2
      ,p_source                VARCHAR2
      ,p_area                  NUMBER 
      ,p_height                NUMBER 
      ,p_density               NUMBER 
      ,p_earth_thickness       NUMBER 
      )
    IS
        v_ct           PLS_INTEGER := 0 ;
    BEGIN
    
        SELECT COUNT(1)
            INTO v_ct 
        FROM pond_info 
        WHERE pond_id = p_pond_id
		 AND  user_id = p_user_id;
            
        IF v_ct = 0 THEN 
            p_flag :=  -1;
            p_ret_msg  := '修改失败，当前用户名下找不到塘口ID为:'||p_pond_id||'的塘口，请检查塘口ID值';
        
        ELSIF v_ct > 1 THEN 
            p_flag :=  -2;
            p_ret_msg  := '修改失败，当前用户名下找到多个相同塘口:'||p_pond_id||'，请与系统管理员联系';
        ELSE   ---- now  v_ct is garanteend = 1 
            
            
            UPDATE pond_info 
                SET  pond_name       	=  p_pond_name      
                    ,prod            	=  p_prod           
                    ,source          	=  p_source         
                    ,area            	=  p_area           
                    ,height          	=  p_height         
                    ,density         	=  p_density          
                    ,earth_thickness    =  p_earth_thickness
            WHERE  pond_id     = p_pond_id 
			  AND  user_id     = p_user_id 
              ;

            v_ct :=   SQL%ROWCOUNT;
            --pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE, 'SQL%ROWCOUNT:'||v_ct  ||
            --                    ',pond_id    :'''||p_o_pond_id    ||''''||
            --                    ',device_type:'''||p_o_device_type||''''||
            --                    ',SIM        :'''||p_o_SIM        ||''''||
            --                    ',status     :'''||p_o_status     ||''''
            --                    );
            --  
              
            IF v_ct = 1 THEN 
                COMMIT;  
                p_flag := 0;
                p_ret_msg := '顺利修改塘口信息，已修改塘口ID：'||p_pond_id ;
            ELSE  ---now 0 row is updated 
                ROLLBACK ;  
                p_flag := 1;
                p_ret_msg := '修改塘口（ID： '||p_pond_id||' ）失败';
            
            END IF ;
        END IF ;
        
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;        
        
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND  THEN 
            p_flag :=  1;
            p_ret_msg  := '修改失败，该塘口不存在，或不在该用户名下，请检查塘口ＩＤ及用户账号';
            p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;        
        WHEN OTHERS  THEN 
            p_flag := -2 ;
            p_ret_msg := '修改塘口失败，原因未知，请联络系统管理人员。错误信息：'|| replace( sqlerrm(), '"', '');
            p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;        
            
    END modify_pond ;
	
	

   PROCEDURE revoke_pond (
       p_flag          OUT   PLS_INTEGER
      ,p_ret_msg       OUT   VARCHAR2 
      ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id             PLS_INTEGER 
      ,p_pond_id             PLS_INTEGER 
      )
    IS
        v_ct           PLS_INTEGER := 0 ;
    BEGIN
    
        SELECT COUNT(1)
            INTO v_ct 
        FROM pond_info 
        WHERE pond_id = p_pond_id
		 AND  user_id = p_user_id;
            
        IF v_ct = 0 THEN 
            p_flag :=  -1;
            p_ret_msg  := '删除失败，当前用户名下找不到塘口ID为:'||p_pond_id||'的塘口，请检查塘口ID值';
        
        ELSIF v_ct > 1 THEN 
            p_flag :=  -2;
            p_ret_msg  := '删除失败，当前用户名下找到多个相同塘口:'||p_pond_id||'，请与系统管理员联系';
        ELSE   ---- now  v_ct is garanteend = 1 
            
            
            UPDATE pond_info 
                SET  user_id  = NULL 
            WHERE  pond_id     = p_pond_id 
			  AND  user_id     = p_user_id 
              ;

            v_ct :=   SQL%ROWCOUNT;
            --pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE, 'SQL%ROWCOUNT:'||v_ct  ||
            --                    ',pond_id    :'''||p_o_pond_id    ||''''||
            --                    ',device_type:'''||p_o_device_type||''''||
            --                    ',SIM        :'''||p_o_SIM        ||''''||
            --                    ',status     :'''||p_o_status     ||''''
            --                    );
            --  
              
            IF v_ct = 1 THEN 
			
				UPDATE device_list
				SET pond_id = 0
				WHERE pond_id = p_pond_id
				;
				
                COMMIT;  
                p_flag := 0;
                p_ret_msg := '顺利删除塘口信息，已删除塘口ID：'||p_pond_id ;
            ELSE  ---now 0 row is updated 
                ROLLBACK ;  
                p_flag := 1;
                p_ret_msg := '修改塘口（ID： '||p_pond_id||' ）失败';
            
            END IF ;
        END IF ;
        
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;        
        
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND  THEN 
            p_flag :=  1;
            p_ret_msg  := ',删除失败，该塘口不存在，或不在该用户名下，请检查塘口ＩＤ及用户账号';
            p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;        
        WHEN OTHERS  THEN 
            p_flag := -2 ;
            p_ret_msg := '删除塘口失败，原因未知，请联络系统管理人员。错误信息：'|| replace( sqlerrm(), '"', '');
            p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;        
            
    END revoke_pond ;	
	
BEGIN
    pkg_debug.SetTopic('PKG_Pond_Info');    
END pkg_pond_info;
/
